
package BackUPDAOS;

import BackUpDTOS.TablaBackUpDTO;
import Herramientas.PasarelaDatos;
import Herramientas.ResultSets;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Alan Gonzalez
 */
public class BackUpEngineDAO {

    private PasarelaDatos db;
    private ResultSets res;

    public BackUpEngineDAO() {

        db = new PasarelaDatos();
        res = new ResultSets();
    }

    /**
     * ALAN GONZALEZ - TODAS LAS TABLAS DE LA BASE DE DATOS EN CURSO
     *
     * @return
     */
    public Vector TableNames() {
        Vector tablas = new Vector();
        db.openDatabase();
        String query = "show tables from optica_demo";
        ResultSet rst = null;
        rst = db.executeQuery(query);
        try {
            while (rst.next()) {
                String tabla = new String();
                tabla = rst.getString("Tables_in_optica_demo");
                tablas.add(tabla);
            }
        } catch (SQLException ex) {
            Logger.getLogger(BackUpEngineDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        db.closeDatabase();
        return tablas;
    }

    /**
     * ALAN GONZALEZ - CODIGO CREATE DE UNA TABLA DETERMINADA
     *
     * @param tableName
     * @return
     */
    public String CeateSourceTable(String tableName) {
        String createSource = new String();
        String query = "show create table " + tableName;
        db.openDatabase();
        ResultSet rst = null;
        rst = db.executeQuery(query);
        try {
            while (rst.next()) {
                createSource = rst.getString("Create Table");
            }
        } catch (SQLException ex) {
            Logger.getLogger(BackUpEngineDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        db.closeDatabase();
        return createSource;
    }

    /**
     * ALAN GONZALEZ - CAMPOS DE UNA TABLA DETERMINADA
     *
     * @param tableName
     * @return
     */
    public Vector tableFields(String tableName) {
        Vector fields = new Vector();
        String query = "show columns from " + tableName;
        ResultSet rst = null;
        db.openDatabase();
        rst = db.executeQuery(query);
        try {
            while (rst.next()) {
            }
        } catch (SQLException ex) {
            Logger.getLogger(BackUpEngineDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        db.closeDatabase();
        return fields;
    }

    /**
     * ALAN GONZALEZ - BACK UP FASE A
     */
    public Vector backUpFaseA() {
        Vector backDTOS = new Vector();
        Vector tables = this.TableNames();
        int cAux = 0;
        for (cAux = 0; cAux < tables.size(); cAux++) {
            TablaBackUpDTO tableBackUp = new TablaBackUpDTO();
            String nametable = (String) tables.elementAt(cAux);
            String createSource = this.CeateSourceTable(nametable);
            Vector fields = this.tableFields(nametable);

            tableBackUp.setTableName(nametable);
            tableBackUp.setTableCreateSoruce(createSource);
            tableBackUp.setTableFields(fields);

            tableBackUp.setTablCurrentRecords(this.currentRecords(fields, nametable));
            backDTOS.add(tableBackUp);
        }
        return backDTOS;
    }

    public Vector currentRecords(Vector fields, String nameTable) {
        Vector currentRecords = new Vector();
        String queryExport = "insert into " + nameTable + " values(";

        // se obtienen los ids de los registros de cada tabla
        db.openDatabase();
        ResultSet rst = null;
        String queryIDS = "select " + nameTable + ".id as id_tabla from " + nameTable + ";";
        Vector ids = new Vector();
        rst = db.executeQuery(queryIDS);
        try {
            while (rst.next()) {
                int id = rst.getInt("id_tabla");
                ids.add(id);
            }
        } catch (SQLException ex) {
            Logger.getLogger(BackUpEngineDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        // recorriendo ids 
        ResultSet rst2 = null;

        int ctrlIds = 0;
        for (ctrlIds = 0; ctrlIds < ids.size(); ctrlIds++) {
            int idReg = (Integer) ids.elementAt(ctrlIds);
            String queryRegs = "select * from " + nameTable + " where id =" + idReg;

            rst2 = db.executeQuery(queryRegs);
            try {
                while (rst2.next()) {

                    int ctrlCampos = 0;
                    for (ctrlCampos = 0; ctrlCampos < fields.size(); ctrlCampos++) {
                        String campo = (String) fields.elementAt(ctrlCampos);

                        ResultSet rst3 = null;
                        rst3 = db.executeQuery("show columns from clientes where Field = '"+campo+"'");
                        String tipoDato = new String();
                        while(rst3.next()){
                           tipoDato = rst3.getString("Field");
                            System.out.println(tipoDato);
                        }

                        
                        if(tipoDato.contains("varchar"))
                            queryExport += "'" + rst2.getString(campo)+ "', "; // CADENA CON APOSTROFE
                        if (tipoDato.contains("int") || tipoDato.contains("double"))
                            queryExport += "" + rst2.getString(campo)+ ", ";   // CADENA SIN APOSTROFE
                        
                    }
                    currentRecords.add(queryExport);
                    System.out.println(queryExport);
                }
            } catch (SQLException ex) {
                Logger.getLogger(BackUpEngineDAO.class.getName()).log(Level.SEVERE, null, ex);
            }

        }

        return currentRecords;
    }
}
